﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data.SqlClient;
using System.Data;

namespace SD.DAL
{
    public class OrganizationDAO : BaseDAO, IOrganizationDAO
    {
        #region IOrganizationDAO Members

        /// <summary>
        /// Get all organization
        /// </summary>
        /// <param name="includeInactive"></param>
        /// <returns></returns>
        public List<Organization> GetAll(Boolean includeInactive)
        {
            // Obtain a SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "OrgGetAll";

            // Add parameter to stored procedure
            comm.Parameters.AddWithValue("@IncludeInactive", includeInactive);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create new list organization
            List<Organization> list = new List<Organization>();

            // add each org to list
            foreach (DataRow row in dt.Rows)
            {
                Organization org = new Organization();
                org.OrgId = row["OrgID"].ToString();
                org.Name = row["Name"].ToString();
                org.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                org.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                org.IsActive = bool.Parse(row["IsActive"].ToString());

                list.Add(org);
            }

            // return list org
            return list;
        }

        /// <summary>
        /// Get org by id
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public Organization GetById(string orgId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "OrgGetById";

            // Add parameter to stored procedure
            comm.Parameters.AddWithValue("@OrgID", orgId);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            Organization org = null;

            // if table has row
            if (dt.Rows.Count > 0)
            {

                org = new Organization();
                org.OrgId = dt.Rows[0]["OrgID"].ToString();
                org.Name = dt.Rows[0]["Name"].ToString();
                org.ShortDescription = dt.Rows[0]["ShortDescription"].ToString();
                org.Address = dt.Rows[0]["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(dt.Rows[0]["AddressID"].ToString());
                org.Contact = dt.Rows[0]["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(dt.Rows[0]["ContactID"].ToString());
                org.IsPreferred = bool.Parse(dt.Rows[0]["IsPreferred"].ToString());
                org.IsExpressionOfInterest = bool.Parse(dt.Rows[0]["IsExpressionOfInterest"].ToString());
                org.FullDescription = dt.Rows[0]["FullDescription"].ToString();
                org.Phone= dt.Rows[0]["Phone"].ToString();
                org.Fax = dt.Rows[0]["Fax"].ToString();
                org.Web = dt.Rows[0]["Web"].ToString();
                org.Charity = dt.Rows[0]["Charity"].ToString();
                org.Company = dt.Rows[0]["Company"].ToString();
                org.IsActive = bool.Parse(dt.Rows[0]["IsActive"].ToString());
                org.BusinessType = dt.Rows[0]["BusinessType"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(dt.Rows[0]["BusinessType"].ToString());
                org.Email = dt.Rows[0]["Email"].ToString();
            }

            // return true if success fully, otherwise false
            return org;            
        }

        public string Insert(Organization org)
        {
            string result;
            
            // Create new command object
            SqlCommand comm = CreateCommand();
            
            // Assign stored procedure
            comm.CommandText = "OrgInsert";

            // Add parameters to commnand
            comm.Parameters.AddWithValue("@Name", org.Name);
            comm.Parameters.AddWithValue("@ShortDescription", org.ShortDescription);
            comm.Parameters.AddWithValue("@ContactID", org.Contact == null ? null : org.Contact.ContactId);
            comm.Parameters.AddWithValue("@AddressID", org.Address.AddressId);
            comm.Parameters.AddWithValue("@IsPreferred", org.IsPreferred);
            comm.Parameters.AddWithValue("@IsExpressionOfInterest", org.IsExpressionOfInterest);
            comm.Parameters.AddWithValue("@FullDescription", org.FullDescription);
            comm.Parameters.AddWithValue("@Phone", org.Phone);
            comm.Parameters.AddWithValue("@Fax", org.Fax);
            comm.Parameters.AddWithValue("@Web", org.Web);
            comm.Parameters.AddWithValue("@Charity", org.Charity);
            comm.Parameters.AddWithValue("@Company", org.Company);
            comm.Parameters.AddWithValue("@IsActive", org.IsActive);
            comm.Parameters.AddWithValue("@BusinessType", org.BusinessType.RefId);
            comm.Parameters.AddWithValue("@Email", org.Email);

            try
            {
                comm.Connection.Open();
                // Execute the stored procedure
                result = comm.ExecuteScalar().ToString();
                comm.Connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return result;
        }

        public bool Update(Organization org)
        {
            int result;

            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();
           
            // Assign stored procedure to command
            comm.CommandText = "OrgUpdate";           

            // Add parameters to command
            comm.Parameters.AddWithValue("@OrgID", org.OrgId);
            comm.Parameters.AddWithValue("@Name", org.Name);
            comm.Parameters.AddWithValue("@ShortDescription", org.ShortDescription);
            comm.Parameters.AddWithValue("@ContactID", org.Contact == null ? null : org.Contact.ContactId);
            comm.Parameters.AddWithValue("@AddressID", org.Address == null ? null : org.Address.AddressId);
            comm.Parameters.AddWithValue("@IsPreferred", org.IsPreferred);
            comm.Parameters.AddWithValue("@IsExpressionOfInterest", org.IsExpressionOfInterest);
            comm.Parameters.AddWithValue("@FullDescription", org.FullDescription);
            comm.Parameters.AddWithValue("@Phone", org.Phone);
            comm.Parameters.AddWithValue("@Fax", org.Fax);
            comm.Parameters.AddWithValue("@Web", org.Web);
            comm.Parameters.AddWithValue("@Charity", org.Charity);
            comm.Parameters.AddWithValue("@Company", org.Company);
            comm.Parameters.AddWithValue("@IsActive", org.IsActive);
            comm.Parameters.AddWithValue("@BusinessType", org.BusinessType == null ? null : org.BusinessType.RefId);
            comm.Parameters.AddWithValue("@Email", org.Email);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }

        public bool Delete(string orgId)
        {
            throw new NotImplementedException();
        }

        public List<Organization> GetByFilter(string filterExpression, bool includeInactive)
        {
            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "OrgGetByFilter";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@IncludeInactive", includeInactive);
            comm.Parameters.AddWithValue("@filterExpression", filterExpression);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            List<Organization> list = new List<Organization>();

            // add each org to list
            foreach (DataRow row in dt.Rows)
            {
                Organization org = new Organization();
                org.OrgId = row["OrgID"].ToString();
                org.Name = row["Name"].ToString();
                org.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                org.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                org.IsActive = bool.Parse(row["IsActive"].ToString());

                list.Add(org);
            }

            // return list organization
            return list;
        }

        public bool SetActive(string OrgId, bool status)
        {
            int result;

            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command
            comm.CommandType = CommandType.Text;
            comm.CommandText = "UPDATE Organization SET IsActive = @IsActive WHERE OrgID = @OrgID";

            comm.Parameters.AddWithValue("@OrgID", OrgId);
            comm.Parameters.AddWithValue("@IsActive", status);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }

        public List<Organization> GetOrgByName(string orgname)
        {
            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "OrgGetByName";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@OrgName", orgname);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            List<Organization> list = new List<Organization>();

            // add each org to list
            foreach (DataRow row in dt.Rows)
            {
                Organization org = new Organization();
                org.OrgId = row["OrgID"].ToString();
                org.Name = row["Name"].ToString();
                org.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                org.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                org.IsActive = bool.Parse(row["IsActive"].ToString());

                list.Add(org);
            }

            // return list organization
            return list;
        }

        #endregion
    }
}
